package ut.data;
import ut.util.Cache;
import ut.res.MessageType;
import java.util.Date;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
public class FreeTask{
	// 以下是關於免費案件的程式
	public static boolean createFreeTask(int employer, int from, int to, String content){
		if(Database.executeTransaction(new String[]{
			"insert into free_task(employer,from_lan,to_lan,content,publish_time,finish_time) values(" + employer + "," + from + "," + to + ",'" + content.replace("'", "''") + "',now(),adddate(now(), 30))",
			"update record set free_task=free_task+1 where id=" + employer
		})){
			// 刪除案主紀錄快取
			Cache.removeRecord(employer);
			return true;
		}else{
			return false;
		}
	}
	public static FreeTask getFreeTask(long id){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from free_task where id=" + id);
			if(rs.next()){
				return new FreeTask(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"), rs.getInt("to_lan"), rs.getString("content"), rs.getTimestamp("publish_time"), rs.getTimestamp("finish_time"), rs.getInt("response_number"), rs.getInt("translator"));
			}else{
				return null;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 計算特定案主免費案件列表的最大頁數
	public static int getMaxFreeTaskPage(int employer, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from free_task where employer=" + employer);
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得特定案主的免費案件列表
	public static FreeTask[] getFreeTasks(int employer, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			FreeTask[] tasks = new FreeTask[pageSize];
			rs = stmt.executeQuery("select * from free_task where employer=" + employer + " order by finish_time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				tasks[index] = new FreeTask(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"), rs.getInt("to_lan"), rs.getString("content"), rs.getTimestamp("publish_time"), rs.getTimestamp("finish_time"), rs.getInt("response_number"), rs.getInt("translator"));
				index++;
			}
			return tasks;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 計算所有免費案件列表的最大頁數
	public static int getMaxFreeTaskPage(int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from free_task where to_days(now()) - to_days(finish_time) < 90");
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得所有免費案件列表
	public static FreeTask[] getFreeTasks(int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			FreeTask[] tasks = new FreeTask[pageSize];
			rs = stmt.executeQuery("select * from free_task where to_days(now()) - to_days(finish_time) < 90 order by finish_time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				tasks[index] = new FreeTask(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"), rs.getInt("to_lan"), rs.getString("content"), rs.getTimestamp("publish_time"), rs.getTimestamp("finish_time"), rs.getInt("response_number"), rs.getInt("translator"));
				index++;
			}
			return tasks;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 取得特定譯者的免費案件列表的最大頁數
	public static int getMaxFreeTaskPage(ut.data.User translator, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from free_response where translator=" + translator.id);
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得特定譯者的免費案件列表，包含該譯者的回應
	public static FreeTask[] getFreeTasks(ut.data.User translator, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			FreeTask[] tasks = new FreeTask[pageSize];
			rs = stmt.executeQuery("select free_response.content as response_content,free_response.publish_time as response_publish_time,free_task.* from free_response,free_task where free_response.translator=" + translator.id + " and free_response.id=free_task.id order by free_task.finish_time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				tasks[index] = new FreeTask(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"), rs.getInt("to_lan"), rs.getString("content"), rs.getTimestamp("publish_time"), rs.getTimestamp("finish_time"), rs.getInt("response_number"), rs.getInt("translator"));
				tasks[index].relatedResponse = new FreeTask.Response(tasks[index].id, translator.id, rs.getString("response_content"), rs.getTimestamp("response_publish_time"));
				index++;
			}
			return tasks;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 挑選出最好的免費案件回應
	public static int pickResponse(long id, int employer, int translator){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);

			// 測試回應的資料是否存在
			rs = stmt.executeQuery("select id from free_response where id=" + id + " and translator=" + translator);
			if(!rs.next()){
				return 0;
			}
			// 測試案件是否符合可挑選的狀態：id 正確、employer 正確、translator 尚未設定
			int result = stmt.executeUpdate("update free_task set finish_time=now(),translator=" + translator + " where id=" + id + " and employer=" + employer + " and translator=0");
			if(result == 0){
				return 0;
			}
			// 更新譯者記錄
			stmt.executeUpdate("update record set free_success=free_success+1 where id=" + translator);
			// 更新案主紀錄
			stmt.executeUpdate("update record set free_task_success=free_task_success+1 where id=" + employer);
			con.commit();
			// 刪除譯者與案主紀錄快取
			Cache.removeRecord(translator);
			Cache.removeRecord(employer);
			// 寄送訊息給譯者
			MessageType messageType = MessageType.getMessageType(MessageType.FREE_TASK_PICKED);
			ut.data.Message.createMessage(translator, MessageType.getFilledContent(messageType, new String[]{id + ""}), messageType);
			return 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	/* Instance Definition */
	public long id;
	public int employer;
	public int fromLan, toLan;
	public String content;
	public Date publishTime, finishTime;
	public int responseNumber;
	public int translator; // 被挑選到的譯者回應
	public FreeTask.Response relatedResponse;
	public FreeTask(long id, int employer, int fromLan, int toLan, String content, Date publishTime, Date finishTime, int responseNumber, int translator){
		this.id = id;
		this.employer = employer;
		this.fromLan = fromLan;
		this.toLan = toLan;
		this.content = content;
		this.publishTime = publishTime;
		this.finishTime = finishTime;
		this.responseNumber = responseNumber;
		this.translator = translator;
		this.relatedResponse = null;
	}
	// 以下是關於免費案件回應的程式
	// 測試是否已經回應過了
	public static boolean checkResponsed(long id, int translator){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select id from free_response where id=" + id + " and translator=" + translator);
			return rs.next();
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 建立免費案件回應
	public static int createResponse(long id, int translator, String content){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 檢查要翻譯的免費案件是否存在，是否過期
			rs = stmt.executeQuery("select employer from free_task where id=" + id + " and now()<finish_time");
			if(rs.next()){
				if(rs.getInt("employer") == translator){ // 翻譯者不可以是案主自己
					return 0;
				}
			}else{
				return 0;
			}
			// 更新免費案件的翻譯數量
			int result = stmt.executeUpdate("update free_task set response_number=response_number+1 where id=" + id);
			if(result == 0){
				return 0;
			}
			// 將翻譯資料加入資料庫
			stmt.executeUpdate("insert into free_response(id,translator,content,publish_time) values(" + id + "," + translator + ",'" + content.replace("'", "''") + "',now())");
			// 更新譯者紀錄
			stmt.executeUpdate("update record set free_response=free_response+1 where id=" + translator);
			con.commit();
			// 刪除譯者紀錄快取
			Cache.removeRecord(translator);
			return 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得免費案件回應列表
	public static FreeTask.Response[] getResponses(long id, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();
			FreeTask.Response[] responses = new FreeTask.Response[pageSize];
			rs = stmt.executeQuery("select * from free_response where id=" + id + " order by publish_time limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				responses[index] = new FreeTask.Response(rs.getLong("id"), rs.getInt("translator"), rs.getString("content"), rs.getTimestamp("publish_time"));
				index++;
			}
			return responses;

		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 取得免費案件的最佳回應
	public static FreeTask.Response getPickedResponse(long id, int translator){
		if(translator == 0){ // 尚未選出最佳回應
			return null;
		}
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from free_response where id=" + id + " and translator=" + translator);
			if(rs.next()){
				return new FreeTask.Response(rs.getLong("id"), rs.getInt("translator"), rs.getString("content"), rs.getTimestamp("publish_time"));
			}else{
				return null;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	/* Inner Response Class */
	public static class Response{
		public long id;
		public int translator;
		public String content;
		public Date publishTime;
		public Response(long id, int translator, String content, Date publishTime){
			this.id = id;
			this.translator = translator;
			this.content = content;
			this.publishTime = publishTime;
		}
	}
}